package bysj.dao;

import bysj.domain.*;
import bysj.service.*;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class GraduateProjectDao {
    private static GraduateProjectDao graduateProjectDao = new GraduateProjectDao();

    private GraduateProjectDao() {
    }

    public static GraduateProjectDao getInstance() {
        return graduateProjectDao;
    }


    public Collection<GraduateProject> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<GraduateProject> graduateProjects = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM graduateProject");
        int totalNum = CommonDao.getInstance().count(connection,"graduateProject");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            int id = resultSet.getInt("id");
            String title = resultSet.getString("title");
            int  graduateProjectType_id = resultSet.getInt("graduateProjectType_id");
            GraduateProjectType graduateProjectType = GraduateProjectTypeService.getInstance().find(graduateProjectType_id);
            int  graduateProjectStatus_id = resultSet.getInt("graduateProjectStatus_id");
            GraduateProjectStatus graduateProjectStatus = GraduateProjectStatusService.getInstance().find(graduateProjectStatus_id);
            int  graduateProjectCategory_id = resultSet.getInt("graduateProjectCategory_id");
            GraduateProjectCategory graduateProjectCategory = GraduateProjectCategoryService.getInstance().find(graduateProjectCategory_id);
            int teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            int student_id = resultSet.getInt("student_id");
            Student student = StudentService.getInstance().find(student_id);
            int graduateProjectSource_id = resultSet.getInt("graduateProjectSource_id");
            GraduateProjectSource graduateProjectSource = GraduateProjectSourceService.getInstance().find(graduateProjectSource_id);
            int graduateProjectTrueness_id = resultSet.getInt("graduateProjectTrueness_id");
            GraduateProjectTrueness graduateProjectTrueness = GraduateProjectTruenessService.getInstance().find(graduateProjectTrueness_id);
            double score = resultSet.getDouble("score");
            int year = resultSet.getInt("year");
            //为department赋值
            GraduateProject graduateProject = new GraduateProject(id,title,graduateProjectCategory,graduateProjectType,graduateProjectStatus,graduateProjectSource,graduateProjectTrueness,student,year,score,teacher);
            //将department添加到departments集合里
            graduateProjects.add(graduateProject);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        //返回departments集合
        return graduateProjects;
    }

    public GraduateProject find(Integer id,Connection connection) throws SQLException {
        GraduateProject graduateProject = null;
        //创建sql语句
        String degree_sql = "SELECT * FROM graduateProject WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql);
        //为预编译对象赋值
        pstmt.setInt(1, id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            String title = resultSet.getString("title");
            int  graduateProjectType_id = resultSet.getInt("graduateProjectType_id");
            GraduateProjectType graduateProjectType = GraduateProjectTypeService.getInstance().find(graduateProjectType_id);
            int  graduateProjectStatus_id = resultSet.getInt("graduateProjectStatus_id");
            GraduateProjectStatus graduateProjectStatus = GraduateProjectStatusService.getInstance().find(graduateProjectStatus_id);
            int  graduateProjectCategory_id = resultSet.getInt("graduateProjectCategory_id");
            GraduateProjectCategory graduateProjectCategory = GraduateProjectCategoryService.getInstance().find(graduateProjectCategory_id);
            int teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            int student_id = resultSet.getInt("student_id");
            Student student = StudentService.getInstance().find(student_id);
            int graduateProjectSource_id = resultSet.getInt("graduateProjectSource_id");
            GraduateProjectSource graduateProjectSource = GraduateProjectSourceService.getInstance().find(graduateProjectSource_id);
            int graduateProjectTrueness_id = resultSet.getInt("graduateProjectTrueness_id");
            GraduateProjectTrueness graduateProjectTrueness = GraduateProjectTruenessService.getInstance().find(graduateProjectTrueness_id);
            double score = resultSet.getDouble("score");
            int year = resultSet.getInt("year");
            //为graduateProject赋值
             graduateProject = new GraduateProject(id,title,graduateProjectCategory,graduateProjectType,graduateProjectStatus,graduateProjectSource,graduateProjectTrueness,student,year,score,teacher);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return graduateProject;
    }

    public boolean update(GraduateProject graduateProject
            ,Connection connection) throws SQLException {
        //准备预编译语句
        String degree_sql = "update graduateProject set title=?,teacher_id=?,graduateProjectType_id=?,graduateProjectCategory_id=?,graduateProjectStatus_id=?,graduateProjectSource_id=?,graduateProjectTrueness_id=?,student_id=?,score=?,year=? where id=" + graduateProject.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(degree_sql);
        //为预编译对象赋值
        preparedStatement.setString(1, graduateProject.getTitle());
        preparedStatement.setInt(2, graduateProject.getTeacher().getId());
        preparedStatement.setInt(3, graduateProject.getGraduateProjectType().getId());
        preparedStatement.setInt(4, graduateProject.getGraduateProjectCategory().getId());
        preparedStatement.setInt(5, graduateProject.getGraduateProjectStatus().getId());
        preparedStatement.setInt(6, graduateProject.getGraduateProjectSource().getId());
        preparedStatement.setInt(7, graduateProject.getGraduateProjectTrueness().getId());
        preparedStatement.setInt(8, graduateProject.getStudent().getId());
        preparedStatement.setDouble(9,graduateProject.getScore());
        preparedStatement.setInt(10,graduateProject.getYear());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }
    public boolean add(GraduateProject graduateProject,Connection connection) throws SQLException {
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into graduateProject(title," +
                "teacher_id,graduateProjectType_id,graduateProjectCategory_id,graduateProjectStatus_id," +
                "graduateProjectSource_id,graduateProjectTrueness_id,student_id,score,year) Values " +
                "(?,?,?,?,?,?,?,?,?,?)");
        //为预编译对象赋值
        preparedStatement.setString(1, graduateProject.getTitle());
        preparedStatement.setInt(2, graduateProject.getTeacher().getId());
        preparedStatement.setInt(3, graduateProject.getGraduateProjectType().getId());
       preparedStatement.setInt(4, graduateProject.getGraduateProjectCategory().getId());
        preparedStatement.setInt(5, graduateProject.getGraduateProjectStatus().getId());
        preparedStatement.setInt(6, graduateProject.getGraduateProjectSource().getId());
        preparedStatement.setInt(7, graduateProject.getGraduateProjectTrueness().getId());
        preparedStatement.setInt(8, graduateProject.getStudent().getId());
        preparedStatement.setDouble(9,graduateProject.getScore());
        preparedStatement.setInt(10,graduateProject.getYear());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String degree_sql = "delete from graduateProject where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }
}
